﻿//**************************************************************************//
// Copyright (C) Abdul Rasheed. All rights Reserved.                        //
// rasheedat.blogspot.com                                                   //
//**************************************************************************//
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using PPHR.Common;
using PPHR.DataAccess;

namespace PPHR.DataLogic
{
    /// <summary>
    /// Common queries
    /// </summary>
    public class CommonDataLogic
    {
        DBConnection dbCon = new DBConnection();
        
        public DataTable GetMasterTable(string CodeGroup)
        {
            DataTable dt = null;
            try
            {
                string sql = "Select Code, Description From MasterCodes Where CodeGroup='" + CodeGroup.Trim() + "'";
                sql += "Union Select ' ' as Code, ' ' As Description Order By Description";
                dt = dbCon.ExecuteQueryAndGetDataTable(sql);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return dt;
        }
        
        public string GetDescriptionForCode(string CodeGroup, string Code)
        {
            string desc = string.Empty;
            try
            {
                string sql = "Select Description From MasterCodes Where CodeGroup='" + CodeGroup.Trim() + "' And Code ='" + Code.Trim() + "'";
                DataTable dt = dbCon.ExecuteQueryAndGetDataTable(sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    desc = dt.Rows[0][0].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return desc;
        }

        public int KeyNumber(string tableName)
        {
            int key = 1;
            try
            {
                string sql = "Select LastNumber From Sequence Where TableName='" + tableName.Trim() + "'";
                DataTable dt = dbCon.ExecuteQueryAndGetDataTable(sql);
                if (dt != null)
                {
                    if (dt.Rows.Count > 0)
                    {
                        key = int.Parse(dt.Rows[0][0].ToString()) + 1;
                        sql = "Update Sequence Set LastNumber = " + key + " Where TableName='" + tableName.Trim() + "'";
                        int i = dbCon.ExecuteNonQuery(sql);
                    }
                    else
                    {
                        sql = "Insert into Sequence Values(1,'" + tableName + "')";
                        key = dbCon.ExecuteNonQuery(sql);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.InnerException.ToString(), ex);
            }
            return key;
        }

        public string replaceInjectionString(string value)
        {
            string result = value;
            string tmp = result.Replace("'", "''");
            result = tmp.Replace(";", ":");
            tmp = result.Replace("--", " ");
            return tmp;
        }
    }
}
